package com.example.children.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.children.DTO.AttendanceTeacherDTO;
import com.example.children.DTO.StudentClassDTO;
import com.example.children.Model.Attendance;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
public interface AttendanceMapper extends BaseMapper<Attendance> {


    @Select("select * from Attendance where student_id=#{studentId} order by date") //根据学生id来查找他的所有的考勤记录
    List<Attendance> selectByStudentId(String studentId);

    @Select("select * from Attendance where date=#{date}")   //查找某个时间的考勤
    List<Attendance> selectByDate(Date date);


    //日期查询
    @Select("SELECT * FROM Attendance WHERE student_id = #{studentId} AND `date` LIKE CONCAT('%', #{date}, '%') ORDER BY date")
    List<Attendance> selectByDateSend(Attendance attendance);

    //admin查询
    @Select("SELECT DATE_FORMAT(a.date,'%Y-%m-%d') as date,a.status,s.id as studentId,s.name as studentName,a.attendance_id as attendanceId,a.status_detail as statusDetail, " +
            "t.id as teacherId,t.teacher_name as teacherName,t.teacher_phone as teacherPhone,c.class_id as classId, " +
            "ct.class_name as className " +
            "FROM Attendance a " +
            "JOIN student s ON a.student_id = s.id " +
            "LEFT JOIN class c ON c.student_id = s.id " +
            "LEFT JOIN classTo ct ON c.class_id = ct.class_id " +
            "LEFT JOIN classteacher cls ON cls.class_id = c.class_id " +
            "LEFT JOIN teacher t ON t.id = cls.teacher_id " +
            "WHERE (#{studentId} IS NULL OR a.student_id LIKE CONCAT('%', #{studentId}, '%')) " +
            "AND (#{date} IS NULL OR a.date LIKE CONCAT('%', #{date}, '%')) " +
            "AND (#{studentName} IS NULL OR s.name LIKE CONCAT('%', #{studentName}, '%')) " +
            "ORDER BY a.date")
    List<AttendanceTeacherDTO> AdminSelectByDateSend(AttendanceTeacherDTO attendance);



    // 查询总记录数
    @Select("SELECT COUNT(*) FROM Attendance s WHERE s.attendance_id IS NOT NULL")
    int countTotalRecords();

    // 手动分页查询
    @Select("SELECT " +
            "s.student_id AS studentId, s.attendance_id AS attendanceId, s.date,ss.name as studentName, " +
            "s.status, s.status_detail as statusDetail, cls.teacher_id AS teacherId, ct.class_name AS className, c.class_id AS classId, " +
            "t.teacher_name as teacherName,t.teacher_phone as teacherPhone "+
            "FROM Attendance s " +
            "JOIN student ss ON s.student_id = ss.id " +
            "left JOIN class c ON s.student_id = c.student_id " +
            "left JOIN classTo ct ON ct.class_id = c.class_id " +
            "left JOIN classteacher cls ON cls.class_id = c.class_id " +
            "left JOIN teacher t ON t.id = cls.teacher_id " +
            "WHERE s.attendance_id IS NOT NULL AND (cls.headteacher = '班主任' OR cls.headteacher IS NULL) " +
            "ORDER BY s.date " +
            "LIMIT #{pageSize} OFFSET #{offset}")
    List<AttendanceTeacherDTO> GetAllAttendanceTeacherDTO(@Param("offset") int offset, @Param("pageSize") int pageSize);






//    //    查询学生的出勤信息
//    @Select("select attendance_id,student_id,date_format(`date`,'%Y-%m-%d') as `date`,status,statusDetail"+
//            "from attendance"+
//            "where student_id=#{studentId}"
//    )
//    List<Attendance> selectAttendance(String studentId);


}
